package com.kaikeba.dao.impl;

import com.kaikeba.bean.Express;
import com.kaikeba.dao.BaseExpressDao;
import com.kaikeba.exception.DuplicateCodeException;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: 平川
 * @Description:
 * @Date Created in 2021-07-29 21:15
 * @Modified By:
 */
public class ExpressDaoMysql implements BaseExpressDao {

    public static final String SQL_CONSOLE="SELECT COUNT(*) data1_size,COUNT(TO_DAYS(intime)=TO_DAYS(NOW())OR NULL ) data1_day,\n" +
            "COUNT(STATUS=0 OR NULL) data2_size,\n" +
            "COUNT(TO_DAYS(intime)=TO_DAYS(NOW()) AND STATUS=0 OR NULL ) data2_day\n" +
            " FROM express";
    //用于查询所有的数据库信息
    public static final String SQL_FIND_ALL="SELECT * FROM EXPRESS";
    //用于分页查询所有的数据库信息
    public static final String SQL_FIND_LIMIT="SELECT * FROM EXPRESS LIMIT ?,?";
    //根据取件码取件
    public static final String SQL_FIND_BY_CODE="SELECT * FROM EXPRESS WHERE CODE=?";
    //根据快递单号取件
    public static final String SQL_FIND_BY_NUMBER="SELECT * FROM EXPRESS WHERE NUMBER=?";
    //根据手机号查询
    public static final String SQL_FIND_BY_USERPHONE="SELECT * FROM EXPRESS WHERE USERPHONE=?";
    //根据状态码获取用户的手机号码
    public static final String SQL_FIND_BY_STATUS="SELECT DISTINCT userphone FROM express WHERE STATUS=?";
    //根据手机号码返回用户昵称
    public static final String SQL_FIND_BY_USERPHONE1 = "SELECT DISTINCT username FROM express WHERE userphone=?";
    //根据用户号码与状态返回用户未取件的个数
    public static final String SQL_FIND_BY_USERPHONE_ANDSTATUS ="SELECT COUNT(*) FROM express WHERE STATUS=? AND userphone =?";

    //跟距用户手机号查询未取件的快递信息
    public static final String SQL_FIND_BY_USERPHONE_AND_STATUS="SELECT * FROM EXPRESS WHERE USERPHONE=? AND STATUS=?";
    //根据录入人查询快递信息
    public static final String SQL_FIND_BY_SYSPHONE="SELECT * FROM EXPRESS WHERE SYSPHONE=?";
    //快递的增加
    public static final String SQL_INSERT="INSERT INTO EXPRESS(NUMBER,username,userphone,company,code,intime,status,sysPhone) VALUES(?,?,?,?,?,now(),0,?)";
    //快递的修改
    public static final String SQL_UPDATE="update express set number=?,username=?,company=?,status=? where id=?";
    //根据id 去修改用户名与手机号
    public static final String SQL_UPDATE_USERNAME_USERPHONE="update express set username=?,userphone=? where id=?";


    //快递的删除
    public static final String SQL_DELETE="delete from express where id=?";
    //快递状态码的改变（取件）
    public static final String SQL_UPDATE_STATUS="update express set status=1,outtime=now(),code=null where code=?";
    /*
    用于查询数据库中的 全部快递（总数，新增），待取件（总数，新增）
    * @return: [{size:总数,day:新增},{size:总数,day:新增}]
    * */
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String, Integer>> data = new ArrayList<Map<String, Integer>>();
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
            state = conn.prepareStatement(SQL_CONSOLE);
            //3.填充占位符
            //4.执行
            res = state.executeQuery();
            if (res.next()) {

                int data1_size = res.getInt("data1_size");
                int data1_day = res.getInt("data1_day");
                int data2_size = res.getInt("data2_size");
                int data2_day = res.getInt("data2_day");
                Map<String, Integer> map1 = new HashMap<>();
                map1.put("data1_size",data1_size);
                map1.put("data1_day",data1_day);
                Map<String, Integer> map2 = new HashMap<>();
                map2.put("data2_size",data2_size);
                map2.put("data2_day",data2_day);

                data.add(map1);
                data.add(map2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);

        }
        //5.关闭资源
        return data;
    }

    /**
     * 用于查询所有快递
     *
     * @param limit
     * @param offset
     * @param pageNumber
     * @param: limit: true:分页查询  false:查询全部
     * @param: offset:起始位置
     * @param: pageNumber:查询条数
     * @return: 查询到快递的集合
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        List<Express> data = new ArrayList<>();
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
            if (limit) {
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                state.setObject(1,offset);
                state.setObject(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            //3.填充占位符
            //4.执行
            res = state.executeQuery();
            while (res.next()){
                int id = res.getInt("id");
                String number = res.getString("number");
                String username = res.getString("username");
                String userphone = res.getString("userphone");
                String company = res.getString("company");
                String code = res.getString("code");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                int status = res.getInt("status");
                String sysPhone = res.getString("sysPhone");
                Express e = new Express(id, number, username, userphone, company, code, intime, outtime, status, sysPhone);
                data.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);
        }
        return data;
    }

    /**
     * 根据取件码查询快递信息
     *
     * @param code 取件码
     * @return 不存在时返回空
     */
    @Override
    public Express findByCode(String code) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
            state = conn.prepareStatement(SQL_FIND_BY_CODE);
            //3.填充占位符
            state.setObject(1,code);
            //4.执行
            res = state.executeQuery();
            if (res.next()){
                int id = res.getInt("id");
                String number = res.getString("number");
                String username = res.getString("username");
                String userphone = res.getString("userphone");
                String company = res.getString("company");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                int status = res.getInt("status");
                String sysPhone = res.getString("sysPhone");
                Express e = new Express(id, number, username, userphone, company, code, intime, outtime, status, sysPhone);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);
        }
        return null;
    }

    /**
     * 根据用户单号查询快递信息
     *
     * @param number 单号
     * @return 不存在时返回空
     */
    @Override
    public Express findByNumber(String number) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
            state = conn.prepareStatement(SQL_FIND_BY_NUMBER);
            //3.填充占位符
            state.setObject(1,number);
            //4.执行
            res = state.executeQuery();
            if (res.next()){
                int id = res.getInt("id");
                String username = res.getString("username");
                String userphone = res.getString("userphone");
                String company = res.getString("company");
                String code = res.getString("code");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                int status = res.getInt("status");
                String sysPhone = res.getString("sysPhone");
                Express e = new Express(id, number, username, userphone, company, code, intime, outtime, status, sysPhone);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);
        }
        return null;
    }

    /**
     * 根据用户手机号查询快递信息
     *
     * @param userPhone 手机号
     * @return 不存在时返回空
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        List<Express> data = new ArrayList<>();
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            //3.填充占位符
            state.setObject(1,userPhone);
            //4.执行
            res = state.executeQuery();
            while (res.next()){
                int id = res.getInt("id");
                String number = res.getString("number");
                String username = res.getString("username");
                String userphone = res.getString("userphone");
                String company = res.getString("company");
                String code = res.getString("code");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                int status = res.getInt("status");
                String sysPhone = res.getString("sysPhone");
                Express e = new Express(id, number, username, userphone, company, code, intime, outtime, status, sysPhone);
                data.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);
        }
        return data;
    }

    /**
     * 根据用户手机号查询未取件的快递信息
     *
     * @param userPhone 手机号
     * @return 不存在时返回空
     */
    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone,int status) {
        List<Express> data = new ArrayList<>();
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE_AND_STATUS);
            //3.填充占位符
            state.setObject(1,userPhone);
            state.setObject(2,status);
            //4.执行
            res = state.executeQuery();
            while (res.next()){
                int id = res.getInt("id");
                String number = res.getString("number");
                String username = res.getString("username");
                String userphone = res.getString("userphone");
                String company = res.getString("company");
                String code = res.getString("code");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                int status1 = res.getInt("status");
                String sysPhone = res.getString("sysPhone");
                Express e = new Express(id, number, username, userphone, company, code, intime, outtime, status1, sysPhone);
                data.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);
        }
        return data;
    }

    /**
     * @param status
     * @根据取件码的状态，返回用户手机号
     */
    @Override
    public List<String> findbyStatus(int status) {

        List<String> data = new ArrayList<>();
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
                state = conn.prepareStatement(SQL_FIND_BY_STATUS);
                state.setObject(1,status);

            //3.填充占位符
            //4.执行
            res = state.executeQuery();
            while (res.next()){
                String userphone = res.getString("userphone");
                data.add(userphone);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);
        }
        return data;
    }

    /**
     * 根据手机号码返回用户昵称
     *
     * @param userPhone
     */
    @Override
    public String findbyUserphone(String userPhone) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE1);
            state.setObject(1,userPhone);

            //3.填充占位符
            //4.执行
            res = state.executeQuery();
            while (res.next()){
                String username = res.getString("username");
                return username;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);
        }
        return null;
    }

    /**
     * 根据取件状态 与 手机号码 去获取 未取件的个数
     *
     * @param userPhone
     * @param status
     */
    @Override
    public int findbyUserphoneAndStatus(String userPhone, int status) {
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE_ANDSTATUS);
            state.setObject(1,status);
            state.setObject(2,userPhone);
            //3.填充占位符
            //4.执行
            res = state.executeQuery();
            while (res.next()){
                int count = res.getInt(1);
                return count;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);
        }
        return 0;
    }

    /**
     * 根据录入人查询快递信息
     *
     * @param sysPhone 录入人
     * @return 不存在时返回空
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        List<Express> data = new ArrayList<>();
        //1.获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            //2.获取预对象
            state = conn.prepareStatement(SQL_FIND_BY_SYSPHONE);
            //3.填充占位符
            state.setObject(1,sysPhone);
            //4.执行
            res = state.executeQuery();
            while (res.next()){
                int id = res.getInt("id");
                String number = res.getString("number");
                String username = res.getString("username");
                String userphone = res.getString("userphone");
                String company = res.getString("company");
                String code = res.getString("code");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                int status = res.getInt("status");
                Express e = new Express(id, number, username, userphone, company, code, intime, outtime, status, sysPhone);
                data.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,res);
        }
        return data;
    }

    /**
     * 快递的录入
     *
     * @parame 快递单号
     * @return 录入结果
     */
    @Override
    public boolean insert(Express e) throws DuplicateCodeException {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        //2.获取预状态通道
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.填充占位符
            state.setObject(1,e.getNumber());
            state.setObject(2,e.getUsername());
            state.setObject(3,e.getUserphone());
            state.setObject(4,e.getCompany());
            state.setObject(5,e.getCode());
            state.setObject(6,e.getSysPhone());
            //4.执行

            return state.executeUpdate()>0?true:false;
        } catch (SQLException e1) {
            //e1.printStackTrace();
            if (e1.getMessage().endsWith("'express.code'")){
                DuplicateCodeException e2 = new DuplicateCodeException(e1.getMessage());
                throw e2;
            }else {
                e1.printStackTrace();
            }
        }finally {
            DruidUtil.close(conn,state,res);
        }
        //3.填充占位符
        return false;
    }

    /**
     * 快递的修改
     *update express set number=?,username=?,company=?,status=? where id=?
     * @param id         快递的id
     * @param newExpress 新的快递的对象（number,company,username,phoneNumber）
     * @return 修改的结果
     */
    @Override
    public boolean update(int id, Express newExpress) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        //2.获取预状态通道
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setObject(1,newExpress.getNumber());
            state.setObject(2,newExpress.getUsername());
            state.setObject(3,newExpress.getCompany());
            state.setObject(4,newExpress.getStatus());
            state.setObject(5,id);
            //执行
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据手机号去修改， 用户名与 手机号码
     *
     * @param id
     * @param username
     * @param userphone
     */
    @Override
    public boolean updateUsernameUserphone(int id, String username, String userphone) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        //2.获取预状态通道
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_USERNAME_USERPHONE);
            state.setObject(1,username);
            state.setObject(2,userphone);
            state.setObject(3,id);

            //执行
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }



    /**
     * 更改快递的状态为1，表示取件完成
     * update express set status=1 where code=?
     * @param code 要修改的快递单号
     * @return 修改的结果 true表示成功， false表示失败
     */
    @Override
    public boolean updateStatus(String code) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        //2.获取预状态通道
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_STATUS);
            state.setObject(1,code);
            //执行
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 删除快递
     *
     * @param id 快递id
     * @return true成功 false 失败
     */
    @Override
    public boolean delete(int id) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        //2.获取预状态通道
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setObject(1,id);
            //执行
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
